package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.PracticeBrushTeacher;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.PracticePlan;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 * 刷题教师 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-10-18
 */
public interface PracticeBrushTeacherMapper extends BaseMapper<PracticeBrushTeacher> {
    @Select({"<script>",
            "SELECT",
            "t01.*,",
            "getSubjectsName ( t01.subjects_id ) AS subjects_name,",
            "getDictName ( t01.year_class_id, 14 ) AS year_class_name,",
            "t02.lesson_num,",
            "IFNULL( t03.finish_num, 0 ) AS finish_num,",
            "t03.option_value,",
            "IFNULL( t03.score, 0 ) AS score,",
            "DATE_FORMAT( t03.created_at, '%Y-%m-%d' ) AS plan_add_date,",
            "t04.type",
            "FROM",
            "(",
            "SELECT",
            "1 AS type,",
            "t1.id,",
            "t1.school_id,",
            "t1.teacher_id,",
            "t1.year_class_id,",
            "t1.subjects_id,",
            "t1.course_type_id AS class_type_id,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t1.status,",
            "t1.finish_date,",
            "DATE_FORMAT( t1.created_at, '%Y-%m-%d' ) AS grade_created_at,",
            "t2.NAME AS teacher_name,",
            "(",
            "SELECT",
            "count( id ) ",
            "FROM",
            "student_grade AS stu_g ",
            "WHERE",
            "stu_g.grade_id = t1.id ",
            "AND stu_g.is_last = 1 ",
            "AND ((",
            "stu_g.reading_status = 1 ",
            ") ",
            "OR ( stu_g.reading_status = 5 AND stu_g.use_course_count &gt; 0 ))) AS stu_grade_count,",
            "1 as teacher_type",
            "FROM",
            "grade AS t1",
            "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
            "WHERE",
            "t1.deleted = 0 ",
            "AND t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.lesson_type = ${lessonType} ",
            "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
            "AND t1.year_class_id in(${yearClassId}) ",
            "</when>",
            "<when test='teacherName!=null and teacherName!= &apos;&apos;'>",
            "AND t2.name like '%${teacherName}%'",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
            "AND t1.course_type_id in (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
            "AND t1.subjects_id in(${subjectsId}) ",
            "</when>",
            "<when test='inTeacherId!=null and inTeacherId!= &apos;&apos;'>",
            "AND (t1.school_id in(${schoolId}) or t1.teacher_id in(${inTeacherId})) ",
            "</when>",
            "GROUP BY",
            "t1.teacher_id,",
            "t1.subjects_id,",
            "t1.year_class_id,",
            "t1.course_type_id ",
            "UNION ALL",
            "SELECT",
            "2 AS type,",
            "t1.id,",
            "t1.school_id,",
            "t1.teacher_id,",
            "t1.year_class_id,",
            "t1.subjects_id,",
            "t1.course_type_id AS class_type_id,",
            "t1.year_part,",
            "t1.quarter_num,",
            "1 AS `status`,",
            "NULL AS finish_date,",
            "NULL AS grade_created_at,",
            "t2.NAME AS teacher_name,",
            "NULL AS stu_grade_count,",
            "t1.teacher_type ",
            "FROM",
            "practice_brush_teacher AS t1",
            "INNER JOIN staff AS t2 ON t1.teacher_id = t2.id ",
            "WHERE",
            "t1.deleted = 0 ",
            "AND t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "<if test='lessonType == 1'>",
                "AND t1.teacher_type = 1 ",
            "</if>",
            "<if test='lessonType == 2 or lessonType == 3'>",
                "AND t1.teacher_type = 2 ",
            "</if>",
            "<when test='yearClassId!=null and yearClassId!= &apos;&apos;'>",
            "AND t1.year_class_id in(${yearClassId}) ",
            "</when>",
            "<when test='teacherName!=null and teacherName!= &apos;&apos;'>",
            "AND t2.name like '%${teacherName}%'",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!= &apos;&apos;'>",
            "AND t1.course_type_id in (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!= &apos;&apos;'>",
            "AND t1.subjects_id in(${subjectsId}) ",
            "</when>",
            "<when test='inTeacherId!=null and inTeacherId!= &apos;&apos;'>",
            "AND (t1.school_id in(${schoolId}) or t1.teacher_id in(${inTeacherId})) ",
            "</when>",
            "GROUP BY",
            "t1.teacher_id,",
            "t1.subjects_id,",
            "t1.year_class_id,",
            "t1.course_type_id,",
            "t1.teacher_type",
            ") AS t01",
            "INNER JOIN practice_task AS t02 ON t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t02.deleted = 0 ",
            "AND (( t01.teacher_type = 1 AND t01.grade_created_at &lt;= t02.task_start_date AND t01.stu_grade_count > 0 ) OR ( t01.teacher_type = 2 ) or (t01.teacher_type = 1 and stu_grade_count is null)) AND (t01.STATUS &lt;&gt; 2 OR (t01.STATUS = 2 AND ( t01.finish_date &gt;= t02.task_start_date ))) ",
            "AND t02.lesson_num BETWEEN ${lessonNumStart} AND ${lessonNumEnd}",
            "INNER JOIN practice_subjects_type AS t05 ON t01.year_part = t05.year_part AND t01.quarter_num = t05.quarter_num AND t01.subjects_id = t05.subjects_id AND t05.deleted = 0",
            "INNER JOIN practice_type AS t04 ON t04.id = t05.practice_type_id  AND t04.teacher_type = t01.teacher_type",
            "<when test='practiceTypeId!=null and practiceTypeId!=&apos;&apos;'>",
            "AND t04.id = ${practiceTypeId}",
            "</when>",
            "LEFT JOIN practice_plan AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "AND t01.year_class_id = t03.year_class_id ",
            "AND t01.subjects_id = t03.subjects_id ",
            "AND t02.lesson_num = t03.lesson_num ",
            "AND t01.class_type_id = t03.class_type_id ",
            "AND t03.deleted = t02.deleted ",
            "AND t01.teacher_id = t03.teacher_id ",
            "and t03.practice_type_id = t05.practice_type_id",
            "ORDER BY teacher_id ASC",
            "</script>"})
    public IPage<PracticeBrushTeacher> getEnterList(IPage<?> iPage,
                                            @Param("yearPart") Integer yearPart,
                                            @Param("quarterNum") Integer quarterNum,
                                            @Param("schoolId") String schoolId,
                                            @Param("lessonType") Integer lessonType,
                                            @Param("yearClassId") String yearClassId,
                                            @Param("classTypeId") String classTypeId,
                                            @Param("subjectsId") String subjectsId,
                                            @Param("lessonNumStart") Integer lessonNumStart,
                                            @Param("lessonNumEnd") Integer lessonNumEnd,
                                            @Param("practiceTypeId") Long practiceTypeId,
                                            @Param("teacherName") String teacherName,
                                            @Param("inTeacherId") Long inTeacherId
    );

}
